/**
 *
 */
package com.storm.lib.util;

import java.io.File;
import java.util.ArrayList;
import java.util.List;


import org.apache.log4j.Logger;

import com.storm.lib.base.BasePo;
import com.storm.lib.base.BaseUserDBPo;
import com.storm.lib.bean.DataCellBean;
import com.storm.lib.bean.DataRowBean;
import com.storm.lib.bean.SheetBean;
import com.storm.lib.bean.template.BaseTemplate;
import com.storm.lib.type.AsType;
import com.storm.lib.type.Constant;
import com.storm.lib.type.DBType;
import com.storm.lib.type.FileType;
import com.storm.lib.type.JavaType;
import com.storm.lib.type.SystemType;
import com.storm.lib.vo.AwardItemVo;
import com.storm.lib.vo.IdNumberVo;


/**
 * 类功能: 数据库工具类
 * @author johnny
 * @version 2010-10-28
 */
public class DBUtil{
	protected final static Logger logger = Logger.getLogger(DBUtil.class);
			/**
			 * 方法功能:插入Sample数据的Sql
			 * 更新时间:2010-12-21, 作者:johnny
			 * @param dbName
			 * @param sheetBean
			 * @return
			 */
			public static List<String> getInsertSampleStrByTagStructure(String dbName, SheetBean sheetBean) {
				if(sheetBean.getSampleColumns()==null || sheetBean.getSampleDatas()==null){
					return null;
				}
				List<String> sqls = new ArrayList<String>();
				List<String> columnArray = new ArrayList<String>();
////				String[] sqls = new String[sheetBean.getSampleDatas().size()];
//				String[] columnArray= sheetBean.getSampleColumns().toArray(new String[sheetBean.getSampleColumns().size()]);
				for (int i = 0; i < sheetBean.getSampleColumns().size(); i++) {
					if(sheetBean.getSampleColumns().get(i)==null){
						continue;
					}
					columnArray.add("`"+sheetBean.getSampleColumns().get(i)+"`");
//					columnArray.get(i)=;
				}
				String columnsStr = "("+StringUtil.implode(columnArray, ",")+")";
				String sqlTemplate="insert into "+dbName+"."+sheetBean.getParameters().get(1)+columnsStr+" values ";

//				for (int i = 0; i < sheetBean.getSampleColumns().size(); i++) {
//					logger.debug(sheetBean.getSampleColumns().get(i));
//				}
				String[] values= new String[columnArray.size()];
				String valueStr="";
				if(sheetBean.getSampleDatas().size()>0){
					sqls.add("truncate "+dbName+"."+sheetBean.getParameters().get(1));
				}
				for (int i = 0; i < sheetBean.getSampleDatas().size(); i++) {
					DataRowBean row=sheetBean.getSampleDatas().get(i);
					for (int j = 0; j < row.getDataCellList().size(); j++) {
						DataCellBean dataCellBean = row.getDataCellList().get(j);
						if(dataCellBean!=null){
//							logger.debug(dataCellBean.getData());
							values[j]=DBUtil.toDBValue(dataCellBean.getData());
							logger.debug(values[j]);
						}
					}
					valueStr="("+StringUtil.implode(values, ",")+")";
					sqls.add(sqlTemplate + valueStr);
					logger.debug(sqlTemplate + valueStr);
//					sqls[i] = ;
				}
//				ObjectUtil.printObject(sqls);
				return sqls;
//				String tableName = (String)sheetBean.getParameters().get(1);
//				File file = FileUtil.getFileByClassPath(FileType.FILE_CLASS_PATH_TEMPLATE_CREATE_TABLE);
//				if(file==null){
//					ExceptionUtil.throwInvalidParamException();
//				}
//				BaseTemplate baseTemplate = new BaseTemplate();
//				String[] fieldStrs=new String[sheetBean.getRows().size()];
////				  `name` VARCHAR(200),
////				  `city_id` INTEGER UNSIGNED,
////				  `time` TIMESTAMP,
////				  `sign_int` INTEGER,
////				  `tiny_i` TINYINT(1) UNSIGNED,
////				  `big_i` BIGINT(20) UNSIGNED,
//				for (int i = 0; i < fieldStrs.length; i++) {
//					DataRowBean dataRowBean = sheetBean.getRows().get(i);
//					String fieldName = (String)dataRowBean.getDataCellList().get(0).getData();
//					String fieldType = (String)dataRowBean.getDataCellList().get(1).getData();
//					Boolean signed = (Boolean)dataRowBean.getDataCellList().get(3).getData();
//					Boolean notNull = (Boolean)dataRowBean.getDataCellList().get(4).getData();
//					Integer fieldSize = (Integer)dataRowBean.getDataCellList().get(2).getData();
//					String comment=dataRowBean.getCommentByDataRowBean();
//
//					String singleField = "`"+fieldName+"` "+fieldType;
//					if(fieldSize!=null){
//						singleField+="("+fieldSize+")";
//					}
//					if(signed==false && (DBUtil.typeNameIsCommonInt(fieldType))){
//						singleField+=" UNSIGNED";
//					}
//					if(notNull==true){
//						singleField+=" NOT NULL";
//					}
////					if(fieldType==DBType.COLUMN_TYPE_TIMESTMP){
////						singleField+="DEFAULT '0000-00-00 00:00:00'";
////					}
//					singleField+=" comment '"+comment+"'";
//					fieldStrs[i] =singleField;
//				}
//				baseTemplate.setParam("fieldStr",StringUtil.implode(fieldStrs,",\n"));
//				baseTemplate.setParam("enginee", DBType.TABLE_DEFAULT_ENGINEE);
//				baseTemplate.setParam("charset1", DBType.CHARSET1);
//				baseTemplate.setParam("charset2", DBType.CHARSET2);
//
//				baseTemplate.setParam("dbName", dbName);
//
//				baseTemplate.setParam("tableName", tableName);
//				baseTemplate.setTemplateFile(file);
//				String sqlValue=baseTemplate.getTemplateStr();
////				sqlValue=sqlValue.replaceAll("\n", " ");
////				sqlValue=sqlValue.replaceAll("\f", " ");
////				sqlValue=sqlValue.replaceAll("\r", " ");
////				logger.debug(sqlValue);
//				LogUtil.debug("创建表"+dbName+"."+tableName);
//				return sqlValue;
			}

			/**
			 * 方法功能:转换为数据库的值
			 * 更新时间:2010-12-22, 作者:johnny
			 * @param data
			 * @return
			 */
			private static String toDBValue(Object data) {
				if(data ==null ){
					return null;
				}
				if(data instanceof String){
					return "'"+DBUtil.replaceQuote((String)data)+"'";
				}
				return data.toString();
			}

			/**
			 * 方法功能:返回基于TagStructure模板的建表脚本
			 * 更新时间:2010-10-28, 作者:johnny
			 * @param sheetBean
			 * @return 基于TagStructure模板的建表脚本
			 */
			public static String getCreateTableStrByTagStructure(String dbName,SheetBean sheetBean) {
				String tableName = (String)sheetBean.getParameters().get(1);
				File file = FileUtil.getFileByClassPath("net/coolbrick/tp/createTableTemplate.tp");
				if(file==null){
					ExceptionUtil.throwInvalidParamException();
				}
				BaseTemplate baseTemplate = new BaseTemplate();
				String[] fieldStrs=new String[sheetBean.getRows().size()];
//				  `name` VARCHAR(200),
//				  `city_id` INTEGER UNSIGNED,
//				  `time` TIMESTAMP,
//				  `sign_int` INTEGER,
//				  `tiny_i` TINYINT(1) UNSIGNED,
//				  `big_i` BIGINT(20) UNSIGNED,
				for (int i = 0; i < fieldStrs.length; i++) {
					DataRowBean dataRowBean = sheetBean.getRows().get(i);
					String fieldName = (String)dataRowBean.getDataCellList().get(0).getData();
					String fieldType = (String)dataRowBean.getDataCellList().get(1).getData();
					Boolean signed = (Boolean)dataRowBean.getDataCellList().get(3).getData();
					Boolean notNull = (Boolean)dataRowBean.getDataCellList().get(4).getData();
					Integer fieldSize = (Integer)dataRowBean.getDataCellList().get(2).getData();
					String comment=dataRowBean.getCommentByDataRowBean();

					String singleField = "`"+fieldName+"` "+fieldType;
					if(fieldSize!=null){
						singleField+="("+fieldSize+")";
					}
					if(signed==false && (DBUtil.typeNameIsCommonInt(fieldType))){
						singleField+=" UNSIGNED";
					}
					if(notNull==true){
						singleField+=" NOT NULL";
					}
//					if(fieldType==DBType.COLUMN_TYPE_TIMESTMP){
//						singleField+="DEFAULT '0000-00-00 00:00:00'";
//					}
					singleField+=" comment '"+comment+"'";
					fieldStrs[i] =singleField;
				}
				baseTemplate.setParam("fieldStr",StringUtil.implode(fieldStrs,",\n"));
				baseTemplate.setParam("enginee", DBType.TABLE_DEFAULT_ENGINEE);
				baseTemplate.setParam("charset1", DBType.CHARSET1);
				baseTemplate.setParam("charset2", DBType.CHARSET2);

				baseTemplate.setParam("dbName", dbName);

				baseTemplate.setParam("tableName", tableName);
				baseTemplate.setTemplateFile(file);
				String sqlValue=baseTemplate.getTemplateStr();
//				sqlValue=sqlValue.replaceAll("\n", " ");
//				sqlValue=sqlValue.replaceAll("\f", " ");
//				sqlValue=sqlValue.replaceAll("\r", " ");
//				logger.debug(sqlValue);
				LogUtil.debug("创建表"+dbName+"."+tableName);
				return sqlValue;
			}



			/**
			 * 方法功能:判断是否为INTEGER,BIGINT,TINYINT等标准类型
			 * 更新时间:2010-10-28, 作者:johnny
			 * @param fieldType
			 * @return
			 */
			public static boolean typeNameIsCommonInt(String fieldType) {
				if(DBType.COLUMN_TYPE_INTEGER.equals(fieldType) || DBType.COLUMN_TYPE_TINYINT.equals(fieldType) || DBType.COLUMN_TYPE_BIGINT.equals(fieldType)){
					return true;
				}
				return false;
			}

			/* (non-Javadoc)
			 * @see com.games.rpg.base.BaseJavaTester#test()
			 */
			public void test() {
//				getCreateTableStrByTagStructure(null, null);
			}


			/**
			 * 方法功能:移除表
			 * 更新时间:2010-10-28, 作者:johnny
			 * @param string
			 * @param string2
			 * @return
			 */
			public static String getDropTableIfExists(String dbName,String tableName) {
				LogUtil.debug("删除表"+dbName+"."+tableName);
				return "drop table if exists "+dbName+"."+tableName;
			}

			/**
			 * 方法功能:丢弃数据库
			 * 更新时间:2010-10-28, 作者:johnny
			 * @param string
			 * @return
			 */
			public static String getDropDatabaseIfExists(String dbName) {
				LogUtil.debug("移除数据库"+dbName);
				return "drop DATABASE if exists `"+dbName+"`";
			}

			/**
			 * 方法功能:创建数据库
			 * 更新时间:2010-10-28, 作者:johnny
			 * @param string
			 * @return
			 */
			public static String getCreateDatabaseIfExists(String dbName) {
				LogUtil.debug("创建数据库"+dbName);
				return "CREATE DATABASE if not exists "+dbName+" DEFAULT CHARACTER SET "+DBType.CHARSET1+" COLLATE "+DBType.CHARSET2;
			}



			/**
			 * 方法功能:根据数据库的字段类型映射出java的基本类型
			 * 更新时间:2010-10-28, 作者:johnny
			 * @param fieldType
			 * @return
			 */
			public static String getJavaTypeNameByDB(String fieldType) {
				if(DBType.COLUMN_TYPE_BIGINT.equals(fieldType)){
					return JavaType.JAVA_TYPE_LONG;
				}
				if(DBType.COLUMN_TYPE_INTEGER.equals(fieldType)){
					return JavaType.JAVA_TYPE_INTEGER;
				}
				if(DBType.COLUMN_TYPE_TINYINT.equals(fieldType)){
					return JavaType.JAVA_TYPE_SHORT;
				}
				if(DBType.COLUMN_TYPE_TIMESTMP.equals(fieldType)){
					return JavaType.JAVA_TYPE_TIMESTAMP;
				}
				if(DBType.COLUMN_TYPE_DATETIME.equals(fieldType)){
					return JavaType.JAVA_TYPE_TIMESTAMP;
				}
				if(DBType.COLUMN_TYPE_VARCHAR.equals(fieldType)){
					return JavaType.JAVA_TYPE_String;
				}
				if(DBType.COLUMN_TYPE_DOUBLE.equals(fieldType)){
					return JavaType.JAVA_TYPE_DOUBLE;
				}
				ExceptionUtil.throwInvalidParamException();
				return null;
			}



			/**
			 * 方法功能:增加逗号分割项,例如1|2|4,1|2|5 增加"1|2|6" 得1|2|4,1|2|5,1|2|6
			 * 更新时间:2010-12-9, 作者:johnny
			 * @param originalStr 原始字符串
			 * @param plusString 附加的字符串
			 */
			public static String getAddedCommerSplitContent(String originalStr,String plusString) {
				if(StringUtil.isEmpty(originalStr) && StringUtil.isEmpty(plusString)){
					return null;
				}
				if(StringUtil.isEmpty(originalStr)){
					return plusString;
				}
				if(StringUtil.isEmpty(plusString)){
					return originalStr;
				}
				return originalStr+","+plusString;

			}



			/**
			 * 方法功能: 1~1 得到1,1 ,2得到 2,2
			 * 更新时间:2010-12-21, 作者:johnny
			 * @param string
			 * @return
			 */
			public static int[] getRangedInts(String reg) {
				String[] items = reg.split("~");
				if(items.length>1){
					return new int[]{Integer.valueOf(items[0]),Integer.valueOf(items[1])};
				}
				return new int[]{Integer.valueOf(reg),Integer.valueOf(reg)};
			}

			/**
			 * 方法功能:返回sql值域的值
			 * 更新时间:2011-1-4, 作者:johnny
			 * @param obj
			 * @return
			 */
			public static String getSqlValueByType(Object obj) {
				if(obj==null){
					ExceptionUtil.throwInvalidParamException();
				}
				if(obj instanceof String){
					return "'"+(String)obj+"'";
				}
				return obj.toString();
			}

			/**
			 * 方法功能:返回数值ID
			 * 更新时间:2011-1-5, 作者:johnny
			 * @param ownHeroes
			 * @return
			 */
			public static int[] getIdsByDBField(String ownHeroes) {
				if(StringUtil.isEmpty(ownHeroes)){
					return new int[0];
				}
				String[] intStrs = ownHeroes.split(",");
				int[] ints = new int[intStrs.length];
				for (int i = 0; i < intStrs.length; i++) {
					ints[i] = Integer.valueOf(intStrs[i]);
				}
				return ints;
			}

			/**
			 * 方法功能:替换为DB名字 {#USER_DB},{#GAME_DB}
			 * 更新时间:2011-3-3, 作者:johnny
			 * @param sql
			 * @return
			 */
			public static String replaceDB(String sql) {
				sql=sql.replace("{#USER_DB}",SystemType.USER_DB_NAME);
				sql=sql.replace("{#GAME_DB}",SystemType.GAME_DB_NAME);
				sql=sql.replace("{#OPENFIRE_DB}",SystemType.GAME_DB_NAME);
				sql=sql.replace("{#USER_LOG_DB}",SystemType.LOG_DB_NAME);
				return sql;
			}

			/**
			 * 方法功能:替换为'
			 * 更新时间:2011-3-3, 作者:johnny
			 * @param sql
			 * @return
			 */
			public static String replaceQuote(String string) {
				string=string.replace("'","\\'");
				return string;
			}

			/**
			 * 方法功能:得到数组
			 * 更新时间:2011-3-10, 作者:johnny
			 * @param skillList
			 * @return
			 */
			public static List<IdNumberVo> getIdNumberList(String str,Boolean idIsFirst) {
				List<IdNumberVo> list=new ArrayList();
				if(StringUtil.isEmpty(str)){
					return list;
				}
				String[] items = str.split(",");
				for (int i = 0; i < items.length; i++) {
					String[] vars = items[i].split("\\|");
					IdNumberVo idNumberVo = new IdNumberVo();
					if(idIsFirst==false){
						idNumberVo.setId(Integer.valueOf(vars[1]));
						idNumberVo.setNum(Integer.valueOf(vars[0]));
					}
					else{
						idNumberVo.setId(Integer.valueOf(vars[0]));
						idNumberVo.setNum(Integer.valueOf(vars[1]));
					}
					list.add(idNumberVo);
				}
				return list;
			}

			/**
			 *
			 * 方法功能:
			 * 更新时间:2011-3-23, 作者:johnny
			 * @param str "例如100001|10,100002|5;100003|1 100003一个+(100001 10个或者 100002 5个)"

			 * @return
			 */
			public static List<AwardItemVo> getAwardItemList(String str) {
				int groupNumber=1;
				List<AwardItemVo> list=new ArrayList();
				if(StringUtil.isEmpty(str)){
					return list;
				}
				//
				String[] itemGroupsStr = str.split(";");
				for (int i = 0; i < itemGroupsStr.length; i++) {
					String[] singleItemStrs = itemGroupsStr[i].split(",");
					//多选
					if(singleItemStrs.length>1){
						for (int j = 0; j < singleItemStrs.length; j++) {
							String[] vars = singleItemStrs[j].split("\\|");
							Integer itemId = Integer.valueOf(vars[0]);
							Integer num = Integer.valueOf(vars[1]);
							AwardItemVo awardItemVo = new AwardItemVo();
							awardItemVo.setGroupId(1);
							awardItemVo.setItemId(itemId);
							awardItemVo.setNumber(num);
							list.add(awardItemVo);
						}
					}
					//单选
					else{
						String itemStr=singleItemStrs[0];
						String[] vars = itemStr.split("\\|");
						Integer itemId = Integer.valueOf(vars[0]);
						Integer num = Integer.valueOf(vars[1]);
						AwardItemVo awardItemVo = new AwardItemVo();
						awardItemVo.setGroupId(null);
						awardItemVo.setItemId(itemId);
						awardItemVo.setNumber(num);
						list.add(awardItemVo);
					}

				}
				return list;
			}

			public static boolean isDBSyncClass(BasePo po) {
				if(po instanceof BaseUserDBPo){
					return true;
				}
				return false;
			}
			
			public static String getAsTypeNameByDB(String fieldType) {
				if(DBType.COLUMN_TYPE_INTEGER.equals(fieldType)){
					return AsType.AS_TYPE_INTEGER;
				}
				if(DBType.COLUMN_TYPE_VARCHAR.equals(fieldType)){
					return AsType.AS_TYPE_STRING;
				}
				if(DBType.COLUMN_TYPE_DOUBLE.equals(fieldType)){
					return AsType.AS_TYPE_NUMBER;
				}
				if(DBType.COLUMN_TYPE_TINYINT.equals(fieldType)){
					return AsType.AS_TYPE_INTEGER;
				}
				if(DBType.COLUMN_TYPE_DATETIME.equals(fieldType)){
					return AsType.AS_TYPE_DATE;
				}
				if(DBType.COLUMN_TYPE_TIMESTMP.equals(fieldType)){
					return AsType.AS_TYPE_DATE;
				}
				if(DBType.COLUMN_TYPE_BIGINT.equals(fieldType)){
					return AsType.AS_TYPE_NUMBER;
				}
				ExceptionUtil.throwInvalidParamException();
				return null;
			}

}
